# 数据库概述

数据库 DB（DataBase）：按照数据结构来组织、存储和管理数据的仓库
数据库管理系统 DBMS（Database Management System）
关系型数据库的三范式（越高的范式数据库冗余越小）
- 第一范式（1NF）：同一列中不能有多个值。1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解。
- 第二范式（2NF）：可以区分每一行数据。2NF 是对记录的唯一性约束，要求记录有唯一标识，即实体的唯一性。
- 第三范式（3NF）：不包含其它表中已包含的非主键信息。3NF 是对字段冗余性的约束，即任何字段不能由其它字段派生出来，它要求字段没有冗余，即不存在传递依赖。
SQL：结构化查询语言（Structured Query Language），是操作和检索关系数据库的标准语言
标准的 SQL 语句分类：
1. 数据查询语言（Data Query Language，DQL）：从表中获得数据，主要关键字：select
2. 数据定义语言（Data Definition Language，DDL）：创建、删除和修改数据库对象，主要关键字：create、alter、drop 和 truncate
3. 数据操作语言（Data Manipulation Language，DML）：添加、删除和修改表中的行，由 insert into、update 和 delete from 三个命令组成
4. 事务处理语言（TPL）：确保被 DML 语句影响的表的所有行及时得以更新，主要关键字：commit、rollback 和 savepoint
5. 数据控制语言（DCL）：为数据库用户授权，或者回收指定用户的权限，主要关键字：grant 和 revoke
6. 指针控制语言（CCL）：对一个或多个表单独行的操作
数据库对象：存储、管理和使用数据的不同结构形式
常见的数据库对象：表、索引、视图、函数、数据字典、约束、存储过程、触发器等
表具有固定的列数和任意的行数
二维表是同类实体的各种属性的集合，每个实体对应于表中的一行（一条记录），表中的列表示字段

# MySQL 数据库

# 数据库操作

MySQL 书写规则
1. 在 MySQL 数据库中，SQL 语句大小写不敏感
2. SQL 语句可单行或多行书写，以 ; 或 \g 或 \G 作为每条命令的结束符
3. 在 SQL 语句中，关键字不能跨多行或缩写
4. 不能使用 MySQL 中的关键字作为标识符，除非用反引号（`）引起来
cmd 命令
1. 启动 MySQL 服务：net start MySQL
2. 停止 MySQL 服务：net stop MySQL
3. 连接 MySQL：mysql -u 账户 -p [密码] -h 主机名 -P 端口
  如：mysql -uroot -padmin -h127.0.0.1 -P3306
  如果连接的数据库服务器在本机上，并且端口是 3306，则可以简写：mysql -uroot -padmin
4. 导出 SQL 脚本：mysqldump -u 账户 -p 密码数据库名称 > 脚本文件地址
  mysqldump -h 127.0.0.1 -uroot -padmin jdbcdemo > C:/shop_bak.sql
  对于全部是 InnoDB 引擎的库，建议使用 mysqldump 备份数据库时添加 --single-transaction 参数（在导数据前会启动一个事务，来确保拿到一致性视图，由于 MVCC 的支持，这个过程中数据可以正常更新）
5. 导入 SQL 脚本：mysql -u 账户 -p 密码数据库名称 < 脚本文件地址
  mysql -uroot -padmin jdbcdemo < C:/shop_bak.sql
MySQL 数据库系统中 4 个系统自带的数据库（information_schema、mysql、performance_schema、sys）不能被修改
关键字和保留字 (opens new window)：关键字用作标识符可以不需要引号，但保留关键字必须加上反引号才能用作标识符（除非标识符在限定名称（数据库名）的句点之后）

MySQL 通讯的数据包大小默认为 4 M，可通过 max_allowed_packet (opens new window) 修改

查看数据库或表的数据大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='db_name' and table_name='table_name';

# 常见存储引擎

MyISAM：拥有较高的插入、查询速度，但不支持事务，不支持外键，不支持行级锁
InnoDB：支持事务，支持外键，支持行级锁，支持热备份，比 MyISAM 处理效率差，且会占用更多的磁盘空间以保留数据和索引

# 支持的列类型 (opens new window)

Row Size Limits (opens new window)：MySQL 记录行长度最大为 64K（The maximum row size for the used table type, not counting BLOBs, is 65535）。The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB (opens new window) and TEXT (opens new window) columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.
Data Type Storage Requirements (opens new window)

整数类型（可分为有符号和无符号两种）：tinyint、int 或 integer、bigint（可指定位宽）
浮点数类型（可分为有符号和无符号两种）：float(p)、float(M,D)、double(M,D)、decimal(M,D)
定点数类型（可分为有符号和无符号两种）：decimal(M,D)

p 表示精度（以位数表示）
M 表示该值的总位数（精度），D 表示小数点后面的位数（标度）
float 和 double 在不指定精度时，默认会按照实际的精度来显示
decimal 在不指定精度时，默认整数为 10，小数为 0
浮点数类型应使用 decimal，禁止使用 float 和 double
字符类型（字符使用单引号引起来）：char(字符数)、varchar(字符数) 、text 系列类型、json（使用 json列->'$.键' 或 json_extract(json列, '$.键')）（str 的字符个数：char_length(str)）

char、varchar 定义的长度不要超过 5000，否则应该定义为 text

varchar 需要使用 1 或 2 个额外字节记录字符串的长度：如果列的最大长度小于或等于 255 字节，则只使用 1 个字节表示，否则使用 2 个字节

使用 varchar(5) 和 varchar(200) 存储 'hello' 的占用磁盘的存储空间是一样的，但 varchar(200) 会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值（尤其是使用临时表进行排序会操作时，会消耗更多的内存）

数据库和表的字符集统一使用 utf8mb4，避免由于字符集转换产生乱码或造成索引失效

存储在 json 列中的 JSON 文档的最大长度受限于 max_allowed_packet 的值；保存到数据库的 JSON 数据并不是以 JSON 文本存储的，而是二进制的格式
日期时间类型（允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符，或者没有间割符）
date （与时区无关，值使用单引号括起来，检索和显示格式为 'YYYY-MM-DD'，如 '2017-01-01'）
time(n)
datetime(n) （与时区无关，值使用引号括起来，检索和显示格式为 'YYYY-MM-DD HH:MM:SS'）
timestamp(n)：时间戳，显示的值依赖于时区，MySQL 服务器、操作系统以及客户端连接时设置的时区，由于类型 timestamp 占用 4 个字节，因此其存储的时间上限只能到 '2038-01-19 03:14:07' UTC

current_timestamp：当要向表执行插入操作时，如果有个 timestamp 或 datetime 类型的字段的默认值为 current_timestamp，则无论这个字段有没有 set 值都插入当前系统时间
on update current_timestamp：使用 on update current_timestamp 放在 timestamp 或 datetime 类型的字段后面，在数据发生更新时该字段将自动更新时间

MySQL 对 time、datetime 和 timestamp 值提供小数秒支持，精度高达微秒（6 位数字），默认 n 为 0；将带有秒小数部分的 time、datetime 或 timestamp 值插入到相同类型但小数位数较少的列中会导致四舍五入
二进制类型：bit （一般用来存储 0 或 1，Java 中的 boolean/Boolean 类型的值）（可指定位宽）

MySQL支持的列类型

# 服务器系统变量

Server System Variables (opens new window)

# 数据库管理语句

查看数据库服务器存在哪些数据库：show databases;
进入指定的数据库：use 数据库名;
创建指定名称的数据库：create database [if not exists] 数据库名 [default charset utf8mb4] [default collate utf8mb4_general_ci];
更改数据库的默认字符集：alter database 数据库名 default character set utf8mb4 default collate utf8mb4_general_ci;
删除数据库：drop database 数据库名;
查看当前的所有连接：show full processlist;

# 常用性能突发事件分析命令

当前数据库的运行的所有线程：show processlist;
当前运行的所有事务：select * from information_schema.INNODB_TRX;
当前事务出现的锁的语句信息：select * from information_schema.INNODB_LOCKS;
锁等待的对应关系：select * from information_schema.INNODB_LOCK_WAITS;
查看哪些表在使用中：show open tables where In_use > 0;
Innodb 状态：show engine innodb status;
锁性能状态：show status like 'innodb_row_lock_%';

# 数据库函数和操作符

# 表达式求值中的类型转换 (opens new window)

当运算符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容
一些转换是隐式发生的，如 MySQL 会根据需要自动将字符串转换为数字，反之亦然

-- 字符串隐式转换为数字的规则：从字符串的左侧开始向右转换，遇到非数字就停止；如果第一个字符就是非数字，最后的结果是 0
-- 2
SELECT 1+'1a';

-- '2 test'
SELECT CONCAT(2,' test');

1
2
3
4
5
6

# 操作符和比较函数 (opens new window)

# 操作符

:=
||, or, xor
&&, and
not
between ... and ..., not between ... and ..., case, when, then, else
=, <=>(null-safe equal), >=, >, <=, <, !=, <>(not equal), is(test a value against a boolean), is not(test a value against a boolean), is null, is not null, like, not like, regexp, in, not in
|
&
<<, >>
-, +
*, /, div, %, mod
^
-（一元减号）, ~（一元比特反转）
!
binary, collate

# 比较函数

expr is null
isnull(expr)：判断 expr 是否为 null，如果为 null 则返回 1，否则返回 0
expr is not null：检验一个值是否为 null
expr not between min and max
expr between min and max：expr 大于或等于 min 且 expr 小于或等于 max，则返回 1，否则返回 0
expr in (value, ...)
expr not in (value, ...)
coalesce(value, ...)：返回值为列表当中的第一个非 NULL 值，在没有非 NULL 值得情况下返回值为 NULL
greatest(value1, value2, ...)：当有 2 或多个参数时，返回值为最大参数（假如任意一个自变量为 null，则 greatest() 的返回值为 null）
least(value1, value2, ...)：当有 2 或多个参数时，返回值为最小的参数（假如任意一个自变量为 null，则 least() 的返回值为 null）

比较函数和操作符：

比较运算的结果为 1 (TRUE)、0 (FALSE) 或 NULL

NULL 与任何值使用 = 比较时结果都为 NULL

假如参数中的一个为 timestamp 或 datetime 列，而其它参数均为常数，则在进行比较前将常数转为 timestamp，（in() 中的参数除外）

在默认状态下，字符串比较不区分大小写

MySQL 使用 PADSPACE 校对规则，在字符串（char、varchar、text）比较时，会忽略字符串末尾的空格字符（like 匹配除外），例如：'a' = 'a '、'a' not like 'a '

在将一个字符串列同一个数字进行比较时， MySQL 不能使用列中的索引进行快速查找

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

1
2
3
4

# 多行函数

多行函数（聚合函数、分组函数）：对多行输入值（一组记录）整体计算，最后只会得到一个结果
分组函数在计算时忽略列中的空值
不能在 where 子句中使用分组函数
expr 可以是变量、常量或数据列
当某一列的值全是 NULL 时，count(col) 的返回结果为 0，但 sum(col) 的返回结果为 NULL

count(字段) 表示统计某个列值不为 NULL 的数量；count(*) 表示统计该表内的所有行数（包括为 NULL 的行）；count(distinct 字段) 表示统计某个列消除重复数据后的的数量。注意：在 Java 中必须使用 long 接收
avg(expr)：计算多行 expr 的平均值
sum(expr)：计算多行 expr 的总和，当某一列的值全是 NULL 时，需注意 NPE 问题：ifnull(sum(col), 0)
max(expr)：计算多行 expr 的最大值
min(expr)：计算多行 expr 的最小值
group_concat([distinct] expr [, expr ...] [order by {unsigned_integer | col_name | expr} [asc | desc] [, col_name ...]] [separator str_val])：用 str_val（默认“,”）连接一个组内的 expr 指定的非 null 值的字符串，可用 col_name 进行组内排序

# 单行函数

单行函数：对每行输入值单独计算，每行得到一个计算结果

# 控制流程函数

case value when compare-value then result [when compare-value then result ...] [else result] end：返回与 compare-value 匹配的结果值，如果没有匹配的结果值，则返回结果为 else 后的结果，如果没有 else 部分，则返回值为 null
case when condition then result [when condition then result ...] [else result] end：返回第一种情况的真实结果，如果没有匹配的结果值，则返回结果为 else 后的结果，如果没有 else 部分，则返回值为 null
if[expr1, expr2, expr3)：如果 expr1 为 true（expr1 不等于 0 且不等于 null），则返回值为 expr2，否则返回值为 expr3
ifnull(expr1, expr2)：如果 expr1 为 null，则返回 expr2，否则返回 expr1
nullif(expr1, expr2)：如果 erpr1 和 expr2 相等，则返回 null，否则返回 expr1
isnull(expr1)：判断 expr1 是否为 null，如果为 null 则返回 true，否则返回 false

# 字符串函数

lower(str)：将 str 中的字母全部变为小写
upper(str)：将 str 中的字母全部变为大写
concat(str1, str2, ...)：字符串连接（若有任何一个参数为 null，则返回值为 null）
concat_ws(separator, str1, str2, ...)：第一个参数是其它参数的分隔符，用分隔符连接字符串，如果分隔符为 null，则结果为 null，函数会忽略任何分隔符参数后的 null 值
repeat(str, count)：重复的 str 字符串 count 次
char_length(str)、character_length(str)：求 str 的字符个数
length(str)：求 str 的字节个数
lpad(str, len, padstr)：用 padstr 左填补 str 使其字符长度 len（若 str 的长度大于 len，则缩短 str 至 len 个字符）
rpad(str, len, padstr)：用 padstr 右填补 str 使其字符长度 len（若 str 的长度大于 len，则缩短 str 至 len 个字符）
ltrim(str)：删除 str 左边空格
rtrim(str)：删除 str 右边空格
trim(str)：删除 str 左右两边空格
replace(str, from_str, to_str)：将 str 中的 from_str 全部替换为 to_str（大小写敏感）
left(str, len)：返回从字符串 str 最左开始的长度为 len 的子字符串
right(str, len)：返回从字符串 str 最右开始的长度为 len 的子字符串
substring(str, pos)：从 str 返回一个子字符串，起始于位置 pos（若 pos 为负数，从 str 尾部开始计算），至 str 最后
substring(str, pos, len)：从 str 返回一个字符长度为 len 的子字符串，起始于位置 pos，同义词：mid(str, pos, len)
find_in_set(str, strlist)：strlist 是一个由一些被“,”符号分开的子链组成的字符串，假如字符串 str 在由 N 子链组成的字符串列表 strlist 中，则返回值的范围在 1 到 N 之间
instr(str, substr)：返回字符串 str 中子字符串 substr 的第一个出现位置
locate(substr, str) , locate(substr, str, pos)：返回字符串 str 中子字符串 substr 的第一个出现位置，可指定起始位置在 pos

# 数值函数

abs(x)：求 x 的绝对值
mod(n, m)：求 n 除以 m 的余数
ceil(x)：求大于 x 的最小整数（向上取整）
floor(x)：求小于 x 的最大整数（向下取整）
round(x) ：用四舍五入对 x 取整
round(x, d)：用四舍五入对 x 值保留到小数点后 d 位；若 d 为负数，表示对 x 的整数部位
truncate(x, d)：截去 x 值第 d 位小数后的数字；若 d 为负数，截去（归零）小数点左起第 d 位开始后面所有低位的值
rand()、rand(n)：返回一个随机浮点值，其范围为 0 ≤ v ≤ 1.0，若已指定一个整数参数 n，则它被用作种子值，用来产生重复序列；若要在 i ≤ r ≤ j 范围得到一个随机整数 r，需要用到表达式 floor(i + rand() * (j – i + 1)) 或 round(i + rand() * (j – i))
```
-- 随机选取一条记录
select *
from `table` as t1 
join (select round(rand() * (select max(id) from `table`)) as id) as t2
where t1.id >= t2.id
order by t1.id asc limit 1;
```
1
2
3
4
5
6

# 日期时间函数

now(n)、current_timestamp()：返回当前系统日期及时间（timestamp with time zone）
curdate()、current_date()：返回当前系统日期
curtime()、current_time()：返回当前系统时间
adddate(date, interval expr type)、date_add(date, interval expr type)：将 date 值添加指定的时间间隔值
subdate(date, interval expr type)、date_sub(date, interval expr type)：将 date 值减去指定的时间间隔值（说明：expr 是一个字符串表达式，用来指定从起始日期添加或减去的时间间隔值；type 为关键词，它指示了表达式被解释的方式，常见 type 值：year、quarters、month、week、day、hour、minute、second）
datediff(expr1, expr2)：求 expr1 减去 expr2 得到的天数
period_add(p, n)：将 n 个月添加到时段 p（格式为 YYMM 或 YYYYMM）上，返回值格式为 YYYYMM
period_diff(p1, p2)：返回时段 P1 和 P2 之间的月份差值，P1 和 P2 的格式应为 YYMM 或 YYYYMM
获取日期时间中某个段
- date(expr) ：提取日期或时间日期表达式 expr 中的日期部分
- year(date)：返回 date 对应的年份，范围是从1000 到9999
- quarter(date)：返回 date 所对应的年中某季度，取值范围为 1 到 4
- month(date)：返回 date 对应的月份，范围时从 1 到 12
- week(date[, mode])：返回 date 所对应的星期序号
- yearweek(date[, mode])：返回 date 的年份及星期序号（mode 参数可以指定每星期起始日究竟是星期天还是星期一，以及返回值范围究竟是 0-53，还是从 1-53，如果忽略 mode 参数，就采用 default_week_format 系统变量值，默认为 0，即第一天是周日，返回值范围 0-53）
- day(date)、dayofmonth(date)：返回 date 参数所对应的一月中的第几天
- dayofweek(date)：返回 date 参数所对应的一年中的某一天，取值范围从 1 到 366
- hour(time)：返回 time 对应的小时数，范围是从 0 到 23
- minute(time)：返回 time 对应的分钟数，范围是从 0 到 59
- last_day(date)：返回该月最后一天对应的日期值
timestamp(expr)：将日期或日期时间表达式 expr 作为日期时间值返回
unix_timestamp(date)：date 值距离 '1970-01-01 00:00:00' gmt 的秒数
to_days(date)：根据给定日期 date，返回自 0 年开始的天数
to_seconds(expr)：根据给定的 expr，返回自 0 年开始的秒数
from_unixtime(unix_timestamp) ：返回 'yyyy-mm-dd hh:mm:ss' 格式的日期
from_unixtime(unix_timestamp, format)：返回指定 format 的日期，如 '%Y-%m-%d %H:%i:%s'
date_format(date, format)：把日期转换为指定 format 格式的字符串，如 '%Y-%m-%d %T'
str_to_date(str, format)：把字符串按 format 格式转换为日期，如 '%Y-%m-%d %H:%i:%s'
to_days(date)：给定一个日期 date，返回一个天数（从年份 0 开始的天数）
from_days(n)：给定某日 n，返回一个 date 值

-- 今天
select * from 表名 where to_days(时间字段名) = to_days(curdate());
-- 昨天
select * from 表名 where to_days(时间字段名) = to_days(curdate()) - 1;
-- 近 7 天
select * from 表名 where to_days(时间字段名) > to_days(curdate()) - 7;
select * from 表名 where date(时间字段名) > date_sub(curdate(), interval 7 day);
-- 本周
select * from 表名 where yearweek(时间字段名) = yearweek(curdate());
-- 上周
select * from 表名 where yearweek(时间字段名) = yearweek(curdate()) - 1;
-- 本月
select * from 表名 where date_format(时间字段名, '%y%m') = date_format(curdate(), '%y-%m');
-- 上个月
select * from 表名 where date_format(时间字段名, '%y%m') = date_format(date_sub(curdate(), interval 1 month), '%y%m');
select * from 表名 where period_diff(date_format(curdate(), '%y%m'), date_format(时间字段名, '%y%m')) = 1;
-- 本季度
select * from 表名 where quarter(时间字段名) = quarter(curdate());
-- 上季度
select * from 表名 where quarter(时间字段名) = quarter(date_sub(curdate(), interval 1 quarter));
-- 本年
select * from 表名 where year(时间字段名) = year(curdate());
-- 上年
select * from 表名 where year(时间字段名) = year(date_sub(curdate(), interval 1 year));

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 加密函数

aes_encrypt(str, key_str)
aes_decrypt(crypt_str, key_str)
采用的是对称加密算法 ASE128
注意：加密后的二进制数据应使用 blob 类型存储

select HEX(AES_ENCRYPT('hello wrold', 'ABC123456'));

select AES_DECRYPT(UNHEX('955B01CD97DB88E876642630E6D991D0'), 'ABC123456');

1
2
3

# Cast 函数

cast(expr as type)、convert(expr, type)、convert(expr using transcoding_name)
可用来获取一个类型的值，并产生另一个类型的值
type 可以是以下值其中的一个：binary[(n)]、char[(n)]、date、datetime、decimal、signed [integer]、time、unsigned [integer]
如：convert('abc' using utf8mb4)：将服务器的默认字符集中的字符串 'abc' 转化为 utf8 字符集中相应的字符串

# 信息函数

database()、schema()：当前数据库名
user()：当前 MySQL 用户名和机主名
version()：MySQL 服务器版本

# JSON 函数 (opens new window)

json_contains：判断是否包含某个 JSON 值
json_contains_path：判断某个路径下是否包 JSON 值
json_extract：提取 JSON 值
json_unquote：去除 JSON 字符串的引号，将值转成 string 类型
column->path：json_extract 的简洁写法
column->>path：json_unquote(column -> path）的简洁写法
json_keys：提取 JSON 中的键值为 JSON 数组
json_search：按给定字符串关键字搜索 JSON，返回匹配的路径
json_array_append：末尾添加数组元素，如果原有值是数值或 JSON 对象，则转成数组后，再添加元素
json_array_insert：插入数组元素
json_insert：插入值（插入新值，但不替换已经存在的旧值）
json_merge_patch：合并的 JSON 文件，免去重复键的值
json_merge_preserve：合并的 JSON 文件，保存重复键
json_remove：删除 JSON 数据
json_replace：替换值（只替换已经存在的旧值）
json_set：设置值（替换旧值，并插入不存在的新值）
json_depth：返回 JSON 文档的最大深度
json_length：返回 JSON 文档的长度
json_type：返回 JSON 值得类型
json_valid：判断是否为合法 JSON 文档
json_pretty：提供格式化后的 JSON 值打印
json_storage_size：返回用于存储 JSON 文档的二进制表示形式的字节数

# 其它函数

format(x, d)：将数字 x 的格式写为 '#,###,###.##' 形式的字符串，以四舍五入的方式保留小数点后 d 位
inet_aton(expr)：将一个作为字符串的网络地址（如 '127.0.0.1'）转换为一个代表该地址数值的整数（2130706433），使用 int unsigned 列存储（Java 中须使用 long 接收）
inet_ntoa(expr)：将一个数字网络地址（4 或 8 比特）转换为一个作为字符串的网络地址
sleep(duration)：睡眠（暂停）时间为 duration 参数给定的秒数，然后返回 0，若 sleep() 被中断，返回 1
uuid()：返回一个通用唯一标识符（UUID）

# 存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一些 SQL 语句的集合
存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句
存储过程和函数是在 MySQL 服务器中存储和执行的，可以减少客户端和服务器端的数据传输

# 创建存储过程和函数

CREATE PROCEDURE sp_name ([[IN | OUT | INOUT] param_name type[,...]])
    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([param_name type [,...]])
    RETURNS type
    [characteristic ...] routine_body

1
2
3
4
5
6

sp_name：存储过程/函数的名称
proc_parameter：存储过程的参数列表，proc_parameter 中的每个参数由 3 部分组成：输入输出类型、参数名称、参数类型。其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型
func_parameter：存储函数的参数列表
RETURNS type：指定返回值的类型
characteristic：指定存储过程的特性，其取值如下：LANGUAGE SQL、[NOT] DETERMINISTIC、{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }、SQL SECURITY { DEFINER | INVOKER }、COMMENT 'string'
routine_body：SQL 代码的内容，可以用 BEGIN…END 来标志 SQL 代码的开始和结束

-- 存储过程名称为 num_from_employee，输入变量为 emp_id，输出变量为 count_num
-- select 语句从 employee 表查询 d_id 值等于 emp_id 的记录，并用 count(*) 计算 d_id 值相同的记录的条数，最后将计算结果存入 count_num 中
delimiter $
create procedure num_from_employee (in emp_id int, out count_num int)
reads sql data
begin
    select count(*) into count_num
    from employee
    where d_id=emp_id ;
end$
delimiter ;

-- 存储函数的名称为 name_from_employee，参数为 emp_id，返回值是 varchar 类型
-- select 语句从 employee 表查询 num 值等于 emp_id 的记录，并将该记录的 name 字段的值返回
delimiter $
create function name_from_employee (emp_id int)
returns varchar(20)
begin
    return (select name
    from employee
    where num=emp_id);
end$
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY idx_name (`name`),
  KEY idx_create_time (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建存储过程循环插入数据
delimiter $
create procedure insert_person()
begin
    declare i int default 1;
    while i <= 1000000 do
    insert into person values(i, concat('name', i), i + 100, date_sub(now(), interval i second));
    set i = i + 1;
    end while;
end$
delimiter ;

call insert_person();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

变量
- 定义变量：DECLARE var_name[, ...] type [DEFAULT value]（没有使用 DEFAULT 子句时，默认值为 NULL）
为变量赋值：SET var_name = expr [, var_name = expr] ...

流程控制构造

-- IF 语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

-- CASE 语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

-- WHILE 语句
WHILE search_condition DO
statement_list
END WHILE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

用在存储过程里的 IF 语句不同于控制流程函数里的 IF() 函数

用在存储程序里的 CASE 语句与“控制流程函数”里描述的 SQL CASE 表达式的 CASE 语句有轻微不同：这里的 CASE 语句不能有 ELSE NULL 子句，并且用 END CASE 替代 END 来终止

# 调用存储过程和函数

调用存储过程：CALL sp_name([parameter[, …]]) ;
调用存储函数：存储函数的使用方法与 MySQL 内部函数的使用方法相同

# 查看存储过程和函数

查看存储过程和函数的状态：SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE 'pattern' ]
查看存储过程和函数的定义：SHOW CREATE { PROCEDURE | FUNCTION } sp_name

# 删除存储过程和函数

DROP { PROCEDURE| FUNCTION } sp_name

# 触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合

# 创建触发器

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tb1_name
FOR EACH ROW
trigger_stmt

1
2
3
4
5

trigger_name：标识触发器名称，用户自行指定
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE
- INSERT：插入某一行时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE 语句触发
- UPDATE：更改某一行时激活触发器，可能通过 UPDATE 语句触发
- DELETE：删除某一行时激活触发器，可能通过 DELETE 或 REPLACE 语句触发
tb1_name：标识建立触发器的表名，即在哪张表上建立触发器
trigger_stmt：触发器程序体，可以是一句 SQL 语句，或者用 BEGIN 和 END 包含的多条语句
BEGIN [statement_list] END，其中 statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾，在 MySQL 中分号是语句结束的标识符，因此需用DELIMITER new_delemiter设置新的分隔符
MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中触发了触发器的那一行数据
- 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据
- 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据
- 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据
- 使用方法：NEW.columnName（columnName 为相应数据表某一列名）
- OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，此时不会再次触发触发器
一个表上最多建立可以建立 6 种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE

-- 创建触发器来使班级表中的班内学生数随着学生的添加自动更新
delimiter $
create trigger ins_stu after insert
on student for each row
begin
    declare c int;
    set c = (select stu_count from class where class_id = new.class_id);
    update class set stu_count = c + 1 where class_id = new.class_id;
end$
delimiter ;

1
2
3
4
5
6
7
8
9
10

# 查看触发器

SHOW TRIGGERS [FROM database_name];

# 删除触发器

DROP TRIGGER [IF EXISTS] [database_name.]trigger_name

# 触发器的执行顺序

对于存储引擎为 InnoDB 的表，若 SQL 语句或触发器执行失败，MySQL 会回滚事务，即：
- 如果 BEFORE 触发器执行失败，SQL 无法正确执行
- SQL 执行失败时，AFTER 型触发器不会触发
- AFTER 类型的触发器执行失败，SQL 会回滚

# 数据定义语言（DDL）

创建表（创建表前先进入某一数据库）

 -- 每个列定义之间用逗号隔开，最后一个列定义后**不需要**写逗号
create table student (
  -- 列名 列类型 [默认值]
  id bigint primary key auto_increment,
  name varchar(20),
  age int
) engine=InnoDB auto_increment=1 default charset=utf8mb4;

-- 复制表结构（不包括外键约束）
create table student_bak like student;
-- 只复制表数据到新表
create table student_bak select * from student;

1
2
3
4
5
6
7
8
9
10
11
12

修改表结构：增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表

 -- 增加列定义
alter table 表名 add (
  新列名 列类型 [默认值],
  ...
);

-- 修改列定义
alter table 表名
modify 列名 列类型 [默认值];

-- 修改表名
RENAME [TO] new_tbl_name
-- ALTER COLUMN 用于指定或删除列的默认值
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
-- CHANGE COLUMN 用于对列进行重命名，可同时更改列的类型
| CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
-- MODIFY COLUMN 用于更改列的类型
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

-- 删除列
alter table 表名
drop 列名;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

除仅修改表名外，alter table 运行时会对原表进行临时复制，在副本上进行更改，然后删除原表，再对新表进行重命名。在执行 alter table 时，其它用户可以查询原表，但是对表的更新和修改的操作将被延迟，直到新表生成为止。新表生成后，这些更新和修改信息会自动转移到新表上。

查看当前数据库中存在哪些表：show tables;
查看表结构：desc 表名; 或 describe 表名;
查看表的详细定义（定义表的 SQL 语句）：show create table 表名;
删除表：drop table 表名;
截断表（删除表里的全部数据，但保留表结构）：truncate 表名;
修改表的存储引擎：alter table 表名 ENGINE='InnoDB';

表的约束（列级约束），关键字之间不用加逗号
1. default '值'：默认值
2. not null：非空约束，该列的内容不能为空
3. unique：唯一约束，在该表中，该列的内容必须唯一，但可以出现多个 null 值
4. primary key：主键约束，相当于非空约束和唯一约束
5. auto_increment[=值]：自增长，只能用于指定整型主键列，默认从 1 开始，步长为 1，向该表插入记录时可不为该列指定值，或指定为 null 或 0（可以通过设置 sql_mode = 'NO_AUTO_VALUE_ON_ZERO' 将自增值设置为 0）（只能有一个自增列，且必须被定义为主键）
6. foreign key (外键列) references 主表 (参考列)：外键约束
  从表外键列的值必须在主表被参照列的值范围之内，或者为 null，要求从表和主表的存储引擎都为 InnoDB

# Online DDL

MySQL 5.5 版本之前（不包括 5.5），对于索引的添加或者删除的这类 DDL 操作，MySQL 数据库的操作过程为：
1. 创建一张新的临时表，表结构为通过命令 ALTER TABLE 新定义的结构
2. 把原表中数据逐行复制到临时表，在此期间会阻塞 DML
3. 删除原表
4. 把临时表重名为原来的表名
MySQL 5.5 版本中对添加索引操作引入了新特性 Fast Index Create（FIC 特性）
MySQL 5.6 版本开始支持 Online DDL（在线数据定义）操作，其允许某些 DDL 操作的同时，还允许 DML 操作
支持的 DDL 类型：添加列、删除列、列名修改、添加索引、修改索引、碎片整理操作等
不支持的 DDL 类型：修改列的类型、修改列的长度、修改字符集、转换字符、分区表操作、时区修正操作等
语法
```
ALTER TABLE tbl_name
| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name, ...) [index_option] ...
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
```
1
2
3
4
- ALGORITHM 指定了创建或删除索引的算法：
  - COPY 表示按照 MySQL 5.5 版本之前的工作模式，即创建临时表的方式
  - INPLACE 表示索引创建或删除操作不需要创建临时表
  - DEFAULT 表示根据参数 old_alter_table 来判断是通过 INPLACE 还是 COPY 的算法，该参数的默认值为 OFF，表示采用 INPLACE 的方式
- LOCK 部分为索引创建或删除时对表添加锁的情况
  - NONE：执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会收到阻塞。因此这种模式可以获得最大的并发度
  - SHARE：执行索引创建或删除操作时，对目标表加上一个 S 锁，对于并发地读事务，依然可以执行，但是遇到写事务，就会发生等待操作
  - EXCLUSIVE：执行索引创建或删除操作时，对目标表加上一个 X 锁，读写事务都不能进行，因此会阻塞所有的线程
  - DEFAULT：先判断当前操作是否可以使用 NONE 模式，若不能，则判断是否可以使用 SHARE 模式，最后判断是否可以使用 EXCLUSIVE 模式（即 DEFAULT 会通过判断事务的最大并发性来判断执行 DDL 的模式）
InnoDB 存储引擎实现 Online DDL 的原理是在执行创建或者删除操作的同时，将 INSERT、UPDATE、DELETE 这类 DML 操作日志写入到一个缓存中，待完成索引创建后再将重做应用到表上，以此达到数据的一致性（缓存的大小由参数 innodb_online_alter_log_max_size 控制，默认的大小为 128MB）
Online DDL Support (opens new window)

# 数据查询语言（DQL）

DQL 操作会返回一个结果集
注意：
1. 字符串和字符串格式的日期要用单引号括起来
2. 数字类型直接书写
3. 字符串是大小写不敏感的，可在需要区分大小写的字符串前添加 binary 关键字
4. 日期值是格式敏感的

# 单表查询

语法

(8)select (9)distinct select_list -- 确定选择的列
(1)from left_table -- 确定查询哪一张表
(3)join_tpye join right_table
(2)on join_condition
(4)where where_condition -- 确定选择的行（不能使用 select 中定义的别名）
(5)group by group_by_list -- 对结果集分组（MySQL 中对查询做了加强，可以使用 select 中定义的别名）
(6)with cube|rollup
(7)having having_condition -- 对分组过滤
(10)order by order_by_list -- 对结果集按照某列排序
(11)limit start_number, limit_number -- 对结果集分页

1
2
3
4
5
6
7
8
9
10

SQL 的执行顺序
1. form：对 from 的左边的表和右边的表计算笛卡尔积，产生虚表 VT1
2. on：对虚表 VT1 进行 on 筛选，只有那些符合 join_condition 的行才会被记录在虚表 VT2 中
3. join：如果指定了 outer join（比如 left join、right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3；如果 from 子句中包含两个以上的表的话，那么就会对上一个 join 连接产生的结果 VT3 和下一个表重复执行步骤 1~3 这三个步骤，一直到处理完所有的表为止
4. where：对虚拟表 VT3 进行 where 条件过滤，只有符合 where_condition 的记录才会被插入到虚拟表 VT4 中
5. group by：根据 group by 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5
6. cube | rollup：对表 VT5 进行 cube 或者 rollup 操作，产生表 VT6
7. having：对虚拟表 VT6 应用 having 过滤，只有符合 having_condition 的记录才会被插入到虚拟表 VT7 中
8. select：执行 select 操作，选择指定的列，插入到虚拟表 VT8 中
9. distinct：对 VT8 中的记录进行去重，产生虚拟表 VT9
10. order by：将虚拟表 VT9 中的记录按照 order_by_list 进行排序操作，产生虚拟表 VT10
11. limit：取出指定行的记录，产生虚拟表 VT11，并将结果返回

# 简单数据查询

select {*, 列 [[as] 别名], ...}
from   表名;

-- 如果列别名中使用关键字，或强制区分大小写，或有空格时，需使用 "" 或 '' 括起来

-- 使用 distinct 关键字从查询结果中清除重复行，必须放在要查询字段的开头
select distinct 列名, ...
from   表名;

-- 实现数学运算查询
-- 对数值型数据列可以使用算算术运算符（+  -  *  /）创建表达式
-- 两个日期之间可以进行减法运算，日期和数值之间可以进行加、减运算

-- 包括空值的任何算术表达式都等于空

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 使用 where 子句限定返回的记录

select <selectList> 
from   表名
where  条件表达式;

-- 优先级：所有的比较运算符、not（!）、and（&&）、or（||）

-- 可以使用 >、>=、<、<=、= 和 <> 等基本的比较运算符比较数值、字符串、日期之间的大小
-- SQL 中判断两个值是否相等的比较运算符是单等号，判断不相等的运算符是 <> 或 !=，SQL 中的赋值运算符是冒号等号（:=）

-- 特殊的比较运算符：between、in、is null、like

-- between 比较运算符，选出某一值域范围（闭区间）的记录
where 列名 between val1 and val2;

-- in 比较运算符，判断列的值是否在指定的集合中
where 列名 in (值1, 值2, ...);
-- 行行比较
where (列名a, 列名b) in ((列a值1, 列b值1), (列a值2, 列b值2), ...)

-- is null 比较运算符、is not null 比较运算符，判断列的值是否为空
where 列名 is null;

-- like 比较运算符，执行通配查询/模糊查询
-- %  表示零或多个任意的字符
-- _  表示一个任意的字符
where 列名 like '_%';
-- 在模式匹配上下文中，'\%' 和 '\_' 用于搜索包含 '%' 和 '_' 的字符串，否则它们将被解释为通配符
-- 即如果在模式匹配上下文之外使用 '\%' 或 '\_'，它们的计算结果为 '\%' 和 '\_'，而不是 '%' 和 '_'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

// 处理模糊查询特殊字符 '%'、'_'
public static String escapeSqlSpecialChar(String keyword) {
    return StringUtils.replace(StringUtils.replace(StringUtils.replace(keyword, "\\",  "\\\\"), "_", "\\_"), "%", "\\%");
}

1
2
3
4

# 使用 order by 子句将结果集进行排序

asc：升序，缺省；desc：降序
注意：当 order by 子句中有使用了带引号的别名时，无法排序
执行方式：如果用于排序的单条记录字段长度 <= max_length_for_sort_data，就使用全字段排序；反之则使用 rowid 排序（需要回表）
如果数据量小则在内存中完成；如果数据量大（超过 sort_buffer_size）则需要借助磁盘来进行排序（使用归并算法把磁盘的临时排好序的小文件合并成一个有序的大文件）
如果多行在 order by 列中具有相同的值，则服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回这些行

select <selectList> 
from   table_name
-- order by field(列名, val1, val2, val3) [asc|desc]：将获取出来的数据根据指定的顺序排序，即该列的其它值（视为相等） < val1 < val2 < val3，其中列名后面的参数自定义，不限制参数个数
order by 列1 [asc|desc], 列2 [asc|desc], field(列3, 值1, 值2, 值3, ...) [asc|desc], ...;

1
2
3
4

# 使用 limit 子句进行分页查询

limit {[offset,] row_count] 或 limit row_count OFFSET offset}
使用两个自变量时，offset 指定返回的第一行的偏移量（初始行的偏移量为 0），row_count 指定返回的行数的最大值
使用一个自变量时，row_count 指定从结果集合的开头返回的行数，即 limit n 与 limit 0, n 等价

-- MySQL 特有
-- limit 子句中不能进行数学运算
-- beginIndex：从结果集中的哪一条索引开始显示（beginIndex 从 0 开始）
-- beginIndex = (当前页数 - 1) * pageSize
-- pageSize：页面大小（每页最多显示多少条记录）
select  <selectList>
from    表名
[where    condition]
limit   beginIndex, pageSize;

1
2
3
4
5
6
7
8
9

# 使用 group by 子句对结果集进行显式分组

将查询结果按某个字段或多个字段进行分组
分组后的结果集隐式按升序排列，如果 group by 语句不指定 order by 条件会导致无谓的排序产生，所以当不需要排序建议添加 order by null
with rollup 关键字将会在所有记录的最后加上一条记录，该记录是上面所有记录的总和
如果查询列表中使用了聚合函数，或者 select 语句中使用了 group by 子句，则要求出现在 select 列表中的字段，要么使用聚合函数或 group_concat() 包起来，要么必须出现在 group by 子句中（MySQL 5.7 之后默认 sql_mode = 'ONLY_FULL_GROUP_BY'）

# 使用 having 子句对分组进行过滤

select [distinct] *|分组字段1[, 分组字段2, …] | 统计函数
from   表名
[where 条件]
group by 分组字段1[, 分组字段2, …] [with cube|rollup]
[having 过滤条件（可以使用统计函数）]

1
2
3
4
5

# 多表连接查询

如果表有别名，则不能再使用表的真名

# Nested-Loop Join Algorithms (opens new window)

MySQL 使用嵌套循环算法或它的变体来执行表之间的连接

# Nested-Loop Join Algorithm

simple nested-loop join (NLJ)：将驱动表/外部表（outer table）的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表（inner table）的过滤条件查询数据，然后合并结果。如果有多表 join，则将前面的表的结果集作为循环数据，取到每行再到连接的下一个表中循环匹配，获取结果集返回给客户端。
由于 NLJ 算法一次只将一行传入内循环，所以外层循环（结果集）有多少行，内层循环就要执行多少次
适用于驱动表的记录集比较小（<10000）而且 inner 表需要有有效的访问方法（Index）
驱动表的记录集一定要小，返回结果集的响应时间是最快的。减少内循环的次数，从而减少 inner table 加载次数。
cost = outer access cost + (inner access cost * outer cardinality)

# Block Nested-Loop Join Algorithm

block nested loop join (BNL) 算法：将外层循环（驱动表）的行/结果集存入 join buffer，内层循环（被驱动表）的每一行与整个 buffer 中的记录做比较，从而减少内层循环的次数。（一次和多条驱动表中的记录做匹配）
BNL 适用于无法使用索引的 equi-joins；从 MySQL 8.0 开始，MySQL 不再使用 BNL，改用 hash join 优化
MySQL join buffering 特点：
- 只有在表扫描类型为 all、index、range 时才可以使用 join buffer
- 第一个 nonconst table 不会分配 join buffer，即便其扫描类型是 all 或 index
- join buffer 中只会保存参与 join 的列，并非整个数据行
- join_buffer_size 变量决定 buffer 大小，默认值是 256K
- 能够被 buffer 的每一个 join 都会分配一个 buffer，即一个 query 最终可能会使用多个 join buffer
- 在 join 之前就会分配 join buffer，在 query 执行完毕即释放

从 MySQL 8.0.18 开始可以使用 hash join 进行连接查询（Hash Join Optimization (opens new window)）

hash join 步骤：将两个表中较小的一个在内存中构造一个 hash 表（对 JOIN KEY），扫描另一个表，同样对 JOIN KEY 进行 hash 后探测是否可以 JOIN。适用于记录集比较大的情况。需要注意的是：如果 hash 表太大，无法一次构造在内存中，则分成若干个 partition，写入磁盘的 temporary segment，则会多一个写的代价，会降低效率。

cost = (outer access cost * # of hash partitions) + inner access cost

# 多表连接查询类型

SQL JOINS

# 内连接查询

-- 1. 隐式内连接：使用 where 指定连接条件，如等值连接（如果没有连接条件，会得到笛卡尔积）
select <selectList>
from   A, B
where  连接条件;

-- 2. 显式内连接查询
select <selectList>
from A [inner] join B on 连接条件;

-- 在做等值连接的时候，若 A 表中和 B 表中的列名相同，则可以简写:
select <selectList>
from A [inner] join B using(同名列);

-- 3. straight_join
-- straight_join 功能同 join 类似，但能让左边的表来驱动右边的表，能改变优化器对于联表查询的执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table.
This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

# 外连接查询

左外连接（left [outer] join）：查询出 join 左边表的全部数据，右边的表不能匹配的数据使用 null 来填充数据
右外连接（right [outer] join）：查询出 join 右边表的全部数据，左边的表不能匹配的数据使用 null 来填充数据
全外连接（full [outer] join）：MySQL 不支持，可以通过 union + 左右连接查询来完成

select <selectList>
from A left|right [outer] join B on 连接条件;

1
2

# 自连接查询

如果同一个表中的不同记录之间存在主、外键约束关联，则需要使用自连接查询
本质：把一个表当成两个表来用，使用别名区分

# 子查询

子查询必须要位于圆括号中
- 不能把同一个表既用于子查询的 from 子句，又用于更新目标，如 update t1 set column2 = (select max(column1) from t1);

# 子查询分类

标量子查询：子查询返回的结果是一个数据（一行一列），当成一个标量值使用，可以使用单行记录比较运算符
列子查询：返回的结果是一列（一列多行），当成一个值列表，需要使用 in、any 和 all 等关键字，any 和 all 可以与 >、>=、<、<=、<>、= 等运算符结合使用
in：与列表中的任意一个值相等
any：与列表中的任意一个值比较，=any、>any、<any
all：与列表中的的每一个值比较，>all、<all

行子查询：返回的结果是一行（一行多列），多个字段合起来作为一个元素（行元素）参与运算

select * from t1 where (column1, column2) = (select column1, column2 from t2);
select * from t1 where row(column1, column2) = (select column1, column2 from t2);

1
2

表子查询：返回的结果是多行多列，一般出现在 from 子句中当成临时表（行内视图）使用，使用前必须给临时表设置别名

-- 在表 t1 中查找同时也存在于表 t2 中的所有的行
select column1, column2, column3 from t1
where (column1, column2, column3) in (select column1, column2, column3 from t2);

1
2
3

exists 子查询：返回的结果 1 或者 0（类似布尔操作），用于检查子查询是否至少会返回一行数据

-- exists 对外表用 loop 逐条查询，每次查询都会查看 exists 的条件语句，当 exists 里的条件语句能够返回记录行时，条件就为真，将该条数据加入查询结果集中；如果没有记录行，条件为假，则丢弃该条数据
select * from a where exists (select 1 from b where b.id = a.id)
-- 等价于
select * from a where id in (select id from b)

-- 外查询表大，子查询表小，用 in；外查询表小，子查询表大，用 exists；若两表大小差不多，则差别不大
-- in 子句在查询的时候有时会被转换为 exists 的方式来执行，变成逐条记录进行遍历
-- MySQL 5.6 已针对子查询做了优化，应该都使用 in，https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
-- 无论哪个表大，用 not exists 都比 not in 要快

1
2
3
4
5
6
7
8
9

# 集合运算

对两个 select 查询得到的结果集进行交（intersect）、并（union）和差（minus）运算
须满足：两个结果集所包含的数据列的数量必须相等，且数据列的数据类型也必须兼容
MySQL 不支持 intersect、minus 运算

-- union/union all 用于把表纵向连接
select column_name(s) from table_name1
union|union all  -- union all 表示允许重复的行（性能高），而 union 会去掉重复的行
select column_name(s) from table_name2

1
2
3
4

# 数据操作语言（DML）

DML 操作返回受影响的行数

# INSERT 语法 (opens new window)

空值用 null 表示
MySQL 特有的语法：用一条 insert 语句插入多条数据记录
如果使用了 ignore，在执行语句时出现的错误被当作警告处理，例如，一个要插入的行与现有的 unique 索引或 primary key 值重复，则该行不被插入，且不会出现错误
如果指定了 on duplicate key update，并且插入行后会导致在一个 unique 索引或 primary key 中出现重复值，则执行旧行 update（受影响行的值：1-如果被作为新行插入；2-如果原有的行被更新；0-如果原有的行被设置为其当前值）
values(col_name) 函数用于引用来自 insert ... on duplicate key update 语句的 insert 部分的列值，只在 insert ... update 语句中有意义，其它时候会返回 null

insert [ignore] into tb1_name (col_name, ...)
values ({expr | default}, ...), (...), ...
[ on duplicate key update col_name = expr, ... ]
[ on duplicate key update col_name = expr | values(col_name) , ... ]

insert into tb1_name
set col_name = {expr | default}, ...
[ on duplicate key update col_name = expr, ... ]

-- 插入查询结果
insert into tb1_name (col_name, ...)
select ...
[ on duplicate key update col_name = expr, ... ]

-- 先判断是否存在这条数据，如果不存在，则正常插入，如果存在，则忽略
insert into … select … where not exist ...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# REPLACE 语法 (opens new window)

如果表中的一个旧记录与一个用于 primary key 或一个 unique 索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除
返回被删除和被插入的行数的和

replace [into] tbl_name [(col_name, ...)]
values ({expr | default}, ...), (...), ...

replace [into] tbl_name
set col_name = {expr | default}, ...

-- 替换查询结果
replace [into] tbl_name [(col_name,...)]
select ...

1
2
3
4
5
6
7
8
9

# UPDATE 语法 (opens new window)

update 语句只支持更新前多少行，不支持从某行到另一行，即只能 limit 30，不能 limit 20, 10

单表语法

update  tb1_name
set col_name1 = expr1 [, col_name2 = expr2 ...]
[where where_definition]
[order by ...]
[limit row_count]

# 批量更新
update categories 
set orderid = case id 
        when 1 then 3 
        when 2 then 4 
        when 3 then 5 
    end, 
    title = case id 
        when 1 then 'new title 1'
        when 2 then 'new title 2'
        when 3 then 'new title 3'
    end
where id in (1, 2, 3);

# 批量更新
update student, (
	(select 1 as id, 'teacher_A' as teacher) union
	(select 2 as id, 'teacher_B' as teacher) union
	(select 3 as id, 'teacher_C' as teacher) union
	(select 4 as id, 'teacher_D' as teacher)
    /* ....more data ... */
    ) as new_student
set
	student.teacher = new_student.teacher
where student.id = new_student.id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

多表语法

update table_references
set col_name1 = expr1 [, col_name2 = expr2 ...]
[where where_definition]

update items, month
set items.price = month.price
where items.id = month.id;
-- 多表 update 语句可以使用在 select 语句中允许的任何类型的联合，比如 left join
-- order by 或 limit 不能与多表 update 同时使用

1
2
3
4
5
6
7
8
9

# DELETE 语法 (opens new window)

使用 delete 语句删除数据时，delete 语句只是将记录的位置或数据页标记为了“可复用”，但是磁盘文件的大小不会改变，即表空间不会直接回收
当使用 delete 语句删除大量表数据后，可以使用 optimize table table_name 释放磁盘空间，优化表期间会锁定表；OPTIMIZE TABLE (opens new window) uses online DDL (opens new window) for regular and partitioned InnoDB tables

单表语法

delete from tb1_name
[where where_definition]
[order by ...]
[limit row_count]

1
2
3
4

多表语法

-- 只删除列于 from 子句之前的表中的对应的行
delete tb1_name [, tb2_name, ...]
from table_references
[where where_definition]

-- 只删除列于 from 子句之中（在 using 子句之前）的表中的对应的行
delete from tb1_name [, tb2_name, ...]
using table_references
[where where_definition]

delete t1, t2 from t1, t2, t3 where t1.id = t2.id and t2.id = t3.id;
delete from t1, t2 using t1, t2, t3 where t1.id = t2.id and t2.id = t3.id;
-- 多表 delete 语句除了使用逗号操作符的内部联合外，还可以使用 select 语句中允许的所有类型的联合，比如 left join

1
2
3
4
5
6
7
8
9
10
11
12
13

# 数据控制语言（DCL）

# 账户管理语句

-- 创建用户
create user 'guest'@'localhost' identified by '1234';
-- 修改用户密码
alter user 'guest'@'localhost' identified by '123';
-- 当前用户设置密码
set password = password('123');
-- 为当前服务器主机上的一个特定账户设置密码
set password for 'guest'@'localhost' = password('123');

-- 授予用户权限
-- 注意：在授权操作之后，需要使用 flush privileges 命令刷新权限
grant 权限 on 数据库.数据库对象 to 用户名@'主机'
identified by '密码' -- 创建用户，设置密码
with grant option; -- 允许用户继续授权

grant all [privileges] on *.* to guest@'localhost' identified by '1234' with grant option;

-- 创建一个超级管理员，用户名为 dev，密码为 1234，只能在 192.168.%.% 登陆，可以给别人授权
grant all privileges on `test_schema`.* to dev@'192.168.%.%' identified by '1234' with grant option;
flush privileges;

-- 查看用户的权限
show grants [for root@localhost]

-- 回收对用户的授权
revoke 权限 on 数据库对象 from 用户;
revoke all on *.* from guest@localhost;

-- 删除用户
drop user 用户名@'主机';
drop user guest@'%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

# SHOW 语法

提供有关数据库、表、列或服务器状态的信息
show [full] processlist：查看哪些线程正在运行，如果不使用 full 关键词，则只显示每个查询的前 100 个字符（如果有 process 权限，可以看到所有线程，否则只能看到自己的线程）
- User：发送 sql 语句到当前 MySQL 使用的是哪个用户
- Host: 发送 sql 语句到当前 MySQL 的主机 ip 和端口
- Db: 连接哪个数据库
- Command: 连接状态，一般是 sleep（休眠空闲）、query（查询）、connect（连接）
- Time: 连接执行时间
- State: 当前 sql 语句的执行状态，如 Checking table（正在检查数据表）、Sending data（正在处理 select 查询的记录，返回数据）、Sorting for group（正在为 group by 做排序）、Sorting for order（正在为 order by 做排序）、Updating（正在搜索匹配的记录，并且修改它们）、Locked（被其它查询锁住了）
show [global | session] variables [like 'pattern']：查看服务器系统变量的值，如 'character%'、'%query_cache%'、'validate_password%'、'%connections'
show [global | session] status [like 'pattern']：查看服务器状态信息，如 'Qcache%'、'Innodb_buffer_pool_%'
show master logs、show binary logs：列出服务器中的二进制日志文件

# SET 语法

用于向用户变量或系统变量赋值
用户变量可以被写作 @var_name，并可以进行如下设置：SET @var_name = expr;
系统变量可以被作为 var_name 或 @@var_name 引用到 SET 语句中
全局变量与会话变量
- 在名称的前面添加 GLOBAL 或 @@global，以指示该变量是全局变量；在名称前面添加 SESSION、@@session 或 @@，以指示该变量是会话变量（LOCAL 和 @@local 是 SESSION 和 @@session 的同义词）；如果没有修改符，则 SET 设置会话变量
- SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
- SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
- 如果使用 SESSION 设置一个系统变量，则该值仍然有效，直到当前会话结束为止；如果使用 GLOBAL 来设置一个系统变量，则该值被记住，并被用于新的连接，直到服务器重新启动为止；如果要进行永久式变量设置，应该把其放入一个选项文件
set names utf8mb4; 把会话系统变量 character_set_client、character_set_connection、character_set_results 设置为给定的字符集（不会修改 character_set_server、character_set_database）

# 其它管理语句

kill [connection | query] thread_id：终止线程，kill connection 与不含修改符的 kill 一样，它会终止与给定的 thread_id 有关的连接；kill query 会终止连接当前正在执行的语句，但是会保持连接的原状（如果有 super 权限，可以终止所有线程和语句，否则只能终止自己的线程）

01 MySQL 02 →